﻿
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;


COMMENT ON DATABASE postgres IS 'default administrative connection database';

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

CREATE EXTENSION IF NOT EXISTS adminpack WITH SCHEMA pg_catalog;

COMMENT ON EXTENSION adminpack IS 'administrative functions for PostgreSQL';

CREATE OR REPLACE FUNCTION public.queries_search_vector_update() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            BEGIN
                NEW.search_vector = ((setweight(to_tsvector('pg_catalog.simple', regexp_replace(coalesce(CAST(NEW.id AS TEXT), ''), '[-@.]', ' ', 'g')), 'B') || setweight(to_tsvector('pg_catalog.simple', regexp_replace(coalesce(NEW.name, ''), '[-@.]', ' ', 'g')), 'A')) || setweight(to_tsvector('pg_catalog.simple', regexp_replace(coalesce(NEW.description, ''), '[-@.]', ' ', 'g')), 'C')) || setweight(to_tsvector('pg_catalog.simple', regexp_replace(coalesce(NEW.query, ''), '[-@.]', ' ', 'g')), 'D');
                RETURN NEW;
            END
            $$;

SET default_tablespace = '';

SET default_with_oids = false;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'access_permissions') = 0
THEN
	CREATE TABLE public.access_permissions (
	    object_type character varying(255) NOT NULL,
	    object_id serial,
	    id serial,
	    access_type character varying(255) NOT NULL,
	    grantor_id integer NOT NULL,
	    grantee_id integer NOT NULL
	);
ALTER TABLE ONLY public.access_permissions
    ADD CONSTRAINT access_permissions_pkey PRIMARY KEY (id);

END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'alembic_version') = 0
THEN
	CREATE TABLE public.alembic_version (
	    version_num character varying(32) NOT NULL
	);
	ALTER TABLE ONLY public.alembic_version
    ADD CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'alert_subscriptions') = 0
THEN
	CREATE TABLE public.alert_subscriptions (
	    updated_at timestamp with time zone NOT NULL,
	    created_at timestamp with time zone NOT NULL,
	    id serial,
	    user_id integer NOT NULL,
	    destination_id integer,
	    alert_id integer NOT NULL,
	    isread boolean NULL default false
	);
	ALTER TABLE ONLY public.alert_subscriptions
    ADD CONSTRAINT alert_subscriptions_pkey PRIMARY KEY (id);
	CREATE UNIQUE INDEX alert_subscriptions_destination_id_alert_id ON public.alert_subscriptions USING btree (destination_id, alert_id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'alerts') = 0
THEN
	CREATE TABLE public.alerts (
	    updated_at timestamp with time zone NOT NULL,
	    created_at timestamp with time zone NOT NULL,
	    id serial,
	    name character varying(255) NOT NULL,
	    query_id integer NOT NULL,
	    contract_id integer NULL default 0,
	    user_id integer NOT NULL,
	    options text NOT NULL,
	    state character varying(255) NOT NULL,
	    last_triggered_at timestamp with time zone,
	    rearm integer
	);
	ALTER TABLE ONLY public.alerts
    ADD CONSTRAINT alerts_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'api_keys') = 0
THEN
	CREATE TABLE public.api_keys (
	    object_type character varying(255) NOT NULL,
	    updated_at timestamp with time zone NOT NULL,
	    created_at timestamp with time zone NOT NULL,
	    id serial,
	    org_id integer NOT NULL,
	    api_key character varying(255) NOT NULL,
	    active boolean NOT NULL,
	    object_id integer NOT NULL,
	    created_by_id integer
	);
	ALTER TABLE ONLY public.api_keys
    ADD CONSTRAINT api_keys_pkey PRIMARY KEY (id);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'changes') = 0
THEN
	CREATE TABLE public.changes (
	    object_type character varying(255) NOT NULL,
	    id serial,
	    object_id integer NOT NULL,
	    object_version integer NOT NULL,
	    user_id integer NOT NULL,
	    change text NOT NULL,
	    created_at timestamp with time zone NOT NULL
	);
	ALTER TABLE ONLY public.changes
    ADD CONSTRAINT changes_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'dashboards') = 0
THEN
	CREATE TABLE public.dashboards (
	    updated_at timestamp with time zone NOT NULL,
	    created_at timestamp with time zone NOT NULL,
	    id serial,
	    version integer NOT NULL,
	    org_id integer NOT NULL,
	    slug character varying(140) NOT NULL,
	    name character varying(100) NOT NULL,
	    user_id integer NOT NULL,
	    layout text NOT NULL,
	    dashboard_filters_enabled boolean NOT NULL,
	    is_archived boolean NOT NULL,
	    is_draft boolean NOT NULL,
	    tags character varying[],
	    profile_image_url character varying(320),
	    timestruct_id integer default 0,
	    timeserial_id integer default 0 
	);
	ALTER TABLE ONLY public.dashboards
    ADD CONSTRAINT dashboards_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'data_source_groups') = 0
THEN
	CREATE TABLE public.data_source_groups (
	    id serial,
	    data_source_id integer NOT NULL,
	    group_id integer NOT NULL,
	    view_only boolean NOT NULL
	);
	ALTER TABLE ONLY public.data_source_groups
    ADD CONSTRAINT data_source_groups_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'data_sources') = 0
THEN
	CREATE TABLE public.data_sources (
	    id serial,
	    org_id integer NOT NULL,
	    name character varying(255) NOT NULL,
	    type character varying(255) NOT NULL,
	    encrypted_options bytea NOT NULL,
	    queue_name character varying(255) NOT NULL,
	    scheduled_queue_name character varying(255) NOT NULL,
	    created_at timestamp with time zone NOT NULL
	);
	ALTER TABLE ONLY public.data_sources
    ADD CONSTRAINT data_sources_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'events') = 0
THEN
	CREATE TABLE public.events (
	    id serial,
	    org_id integer NOT NULL,
	    user_id integer,
	    action character varying(255) NOT NULL,
	    object_type character varying(255) NOT NULL,
	    object_id character varying(255),
	    additional_properties text,
	    created_at timestamp with time zone NOT NULL
	);
	ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'favorites') = 0
THEN
	CREATE TABLE public.favorites (
	    updated_at timestamp with time zone NOT NULL,
	    created_at timestamp with time zone NOT NULL,
	    id serial,
	    org_id integer NOT NULL,
	    object_type character varying(255) NOT NULL,
	    object_id integer NOT NULL,
	    user_id integer NOT NULL
	);
	ALTER TABLE ONLY public.favorites
    ADD CONSTRAINT favorites_pkey PRIMARY KEY (id);
	ALTER TABLE ONLY public.favorites
	    ADD CONSTRAINT unique_favorite UNIQUE (object_type, object_id, user_id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'groups') = 0
THEN
	CREATE TABLE public.groups (
	    id serial,
	    org_id integer NOT NULL,
	    type character varying(255) NOT NULL,
	    name character varying(100) NOT NULL,
	    permissions character varying(255)[] NOT NULL,
	    created_at timestamp with time zone NOT NULL
	);
	ALTER TABLE ONLY public.groups
    ADD CONSTRAINT groups_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'notification_destinations') = 0
THEN
	CREATE TABLE public.notification_destinations (
	    id serial,
	    org_id integer NOT NULL,
	    user_id integer NOT NULL,
	    name character varying(255) NOT NULL,
	    type character varying(255) NOT NULL,
	    options text NOT NULL,
	    created_at timestamp with time zone NOT NULL
	);
	ALTER TABLE ONLY public.notification_destinations
    ADD CONSTRAINT notification_destinations_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'organizations') = 0
THEN
	CREATE TABLE public.organizations (
	    updated_at timestamp with time zone NOT NULL,
	    created_at timestamp with time zone NOT NULL,
	    id serial,
	    name character varying(255) NOT NULL,
	    slug character varying(255) NOT NULL,
	    settings text NOT NULL
	);
	ALTER TABLE ONLY public.organizations
    ADD CONSTRAINT organizations_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'queries') = 0
THEN
	CREATE TABLE public.queries (
	    updated_at timestamp with time zone NOT NULL,
	    created_at timestamp with time zone NOT NULL,
	    id serial,
	    version integer NOT NULL,
	    org_id integer NOT NULL,
	    data_source_id integer,
	    latest_query_data_id integer,
	    name character varying(255) NOT NULL,
	    description character varying(4096),
	    query text NOT NULL,
	    query_hash character varying(32) NOT NULL,
	    api_key character varying(40) NOT NULL,
	    user_id integer NOT NULL,
	    last_modified_by_id integer,
	    is_archived boolean NOT NULL,
	    is_draft boolean NOT NULL,
	    schedule text,
	    schedule_failures integer NOT NULL,
	    options text NOT NULL,
	    search_vector tsvector,
	    tags character varying[],
	    timestruct_id integer default 0,
	    timeserial_id integer default 0 
	);
	ALTER TABLE ONLY public.queries
    ADD CONSTRAINT queries_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'query_results') = 0
THEN
	CREATE TABLE public.query_results (
	    id serial,
	    org_id integer NOT NULL,
	    data_source_id integer NOT NULL,
	    query_hash character varying(32) NOT NULL,
	    query text NOT NULL,
	    data text NOT NULL,
	    runtime double precision NOT NULL,
	    retrieved_at timestamp with time zone NOT NULL,
	    timeserial_id integer,
	    orgstruct_id integer
	);
	ALTER TABLE ONLY public.query_results
    ADD CONSTRAINT query_results_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'query_snippets') = 0
THEN
	CREATE TABLE public.query_snippets (
	    updated_at timestamp with time zone NOT NULL,
	    created_at timestamp with time zone NOT NULL,
	    id serial,
	    org_id integer NOT NULL,
	    trigger character varying(255) NOT NULL,
	    description text NOT NULL,
	    user_id integer NOT NULL,
	    snippet text NOT NULL
	);
	ALTER TABLE ONLY public.query_snippets
    ADD CONSTRAINT query_snippets_pkey PRIMARY KEY (id);
	ALTER TABLE ONLY public.query_snippets
    ADD CONSTRAINT query_snippets_trigger_key UNIQUE (trigger);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'users') = 0
THEN
	CREATE TABLE public.users (
	    updated_at timestamp with time zone NOT NULL,
	    created_at timestamp with time zone NOT NULL,
	    id serial,
	    org_id integer NOT NULL,
	    name character varying(320) NOT NULL,
	    code character varying(50),
	    email character varying(255) NOT NULL,
	    profile_image_url character varying(320),
	    password_hash character varying(128),
	    groups integer[],
	    menustyle_enum character varying(100),
	    annex text DEFAULT ''::varchar(255)   NULL,
	    api_key character varying(40) NOT NULL,
	    disabled_at timestamp with time zone,
	    details json DEFAULT '{}'::json,
	    mobile character varying(15),
            responsible  integer NULL default 0,
	    orgunit_id integer
	);
	ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);
	ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_api_key_key UNIQUE (api_key);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'visualizations') = 0
THEN
	CREATE TABLE public.visualizations (
	    updated_at timestamp with time zone NOT NULL,
	    created_at timestamp with time zone NOT NULL,
	    id serial,
	    type character varying(100) NOT NULL,
	    query_id integer NOT NULL,
	    name character varying(255) NOT NULL,
	    description character varying(4096),
	    options text NOT NULL
	);
	ALTER TABLE ONLY public.visualizations
    ADD CONSTRAINT visualizations_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'widgets') = 0
THEN
	CREATE TABLE public.widgets (
	    updated_at timestamp with time zone NOT NULL,
	    created_at timestamp with time zone NOT NULL,
	    id serial,
	    visualization_id integer,
	    text text,
	    width integer NOT NULL,
	    options text NOT NULL,
	    dashboard_id integer NOT NULL
	);
	ALTER TABLE ONLY public.widgets
    ADD CONSTRAINT widgets_pkey PRIMARY KEY (id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='alert_subscriptions_destination_id_alert_id') = 0
THEN
	CREATE UNIQUE INDEX alert_subscriptions_destination_id_alert_id ON public.alert_subscriptions USING btree (destination_id, alert_id);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='api_keys_object_type_object_id') = 0
THEN
	CREATE INDEX api_keys_object_type_object_id ON public.api_keys USING btree (object_type, object_id);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='data_sources_org_id_name') = 0
THEN
	CREATE INDEX data_sources_org_id_name ON public.data_sources USING btree (org_id, name);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='ix_api_keys_api_key') = 0
THEN
	CREATE INDEX ix_api_keys_api_key ON public.api_keys USING btree (api_key);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='ix_dashboards_is_archived') = 0
THEN
	CREATE INDEX ix_dashboards_is_archived ON public.dashboards USING btree (is_archived);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='ix_dashboards_is_draft') = 0
THEN
	CREATE INDEX ix_dashboards_is_draft ON public.dashboards USING btree (is_draft);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='ix_dashboards_slug') = 0
THEN
	CREATE INDEX ix_dashboards_slug ON public.dashboards USING btree (slug);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='ix_queries_is_archived') = 0
THEN
	CREATE INDEX ix_queries_is_archived ON public.queries USING btree (is_archived);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='ix_queries_is_draft') = 0
THEN
	CREATE INDEX ix_queries_is_draft ON public.queries USING btree (is_draft);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='ix_queries_search_vector') = 0
THEN
	CREATE INDEX ix_queries_search_vector ON public.queries USING gin (search_vector);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='ix_query_results_query_hash') = 0
THEN
	CREATE INDEX ix_query_results_query_hash ON public.query_results USING btree (query_hash);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='ix_widgets_dashboard_id') = 0
THEN
	CREATE INDEX ix_widgets_dashboard_id ON public.widgets USING btree (dashboard_id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='notification_destinations_org_id_name') = 0
THEN
	CREATE UNIQUE INDEX notification_destinations_org_id_name ON public.notification_destinations USING btree (org_id, name);

END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='users_org_id_email') = 0
THEN
	CREATE UNIQUE INDEX users_org_id_email ON public.users USING btree (org_id, email);

END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='users_org_id_code') = 0 
THEN
	CREATE UNIQUE INDEX users_org_id_code ON public.users USING btree (org_id, code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='users_org_id_mobile') = 0 
THEN
	CREATE UNIQUE INDEX users_org_id_mobile ON public.users USING btree (org_id, mobile);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_indexes where indexname='users_org_id_email') = 0
THEN
	CREATE UNIQUE INDEX users_org_id_email ON public.users USING btree (org_id, email);

END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc From pg_trigger where tgname='queries_search_vector_trigger') = 0 
THEN
	CREATE TRIGGER queries_search_vector_trigger BEFORE INSERT OR UPDATE ON public.queries FOR EACH ROW EXECUTE PROCEDURE public.queries_search_vector_update();
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='access_permissions_grantee_id_fkey') = 0
THEN
	ALTER TABLE ONLY public.access_permissions
    ADD CONSTRAINT access_permissions_grantee_id_fkey FOREIGN KEY (grantee_id) REFERENCES public.users(id);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='access_permissions_grantor_id_fkey') = 0
THEN
	ALTER TABLE ONLY public.access_permissions
    ADD CONSTRAINT access_permissions_grantor_id_fkey FOREIGN KEY (grantor_id) REFERENCES public.users(id);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='alert_subscriptions_alert_id_fkey') = 0
THEN
	ALTER TABLE ONLY public.alert_subscriptions
    ADD CONSTRAINT alert_subscriptions_alert_id_fkey FOREIGN KEY (alert_id) REFERENCES public.alerts(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='alert_subscriptions_destination_id_fkey') = 0
THEN
	ALTER TABLE ONLY public.alert_subscriptions
    ADD CONSTRAINT alert_subscriptions_destination_id_fkey FOREIGN KEY (destination_id) REFERENCES public.notification_destinations(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='alert_subscriptions_user_id_fkey') = 0
THEN
ALTER TABLE ONLY public.alert_subscriptions
    ADD CONSTRAINT alert_subscriptions_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='alerts_query_id_fkey') = 0
THEN
ALTER TABLE ONLY public.alerts
    ADD CONSTRAINT alerts_query_id_fkey FOREIGN KEY (query_id) REFERENCES public.queries(id);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='alerts_user_id_fkey') = 0
THEN
ALTER TABLE ONLY public.alerts
    ADD CONSTRAINT alerts_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='api_keys_created_by_id_fkey') = 0
THEN
ALTER TABLE ONLY public.api_keys
    ADD CONSTRAINT api_keys_created_by_id_fkey FOREIGN KEY (created_by_id) REFERENCES public.users(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='changes_user_id_fkey') = 0
THEN
ALTER TABLE ONLY public.changes
    ADD CONSTRAINT changes_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='dashboards_org_id_fkey') = 0
THEN
ALTER TABLE ONLY public.dashboards
    ADD CONSTRAINT dashboards_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.organizations(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='dashboards_user_id_fkey') = 0
THEN
ALTER TABLE ONLY public.dashboards
    ADD CONSTRAINT dashboards_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='data_source_groups_data_source_id_fkey') = 0
THEN
ALTER TABLE ONLY public.data_source_groups
    ADD CONSTRAINT data_source_groups_data_source_id_fkey FOREIGN KEY (data_source_id) REFERENCES public.data_sources(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='data_source_groups_group_id_fkey') = 0
THEN
ALTER TABLE ONLY public.data_source_groups
    ADD CONSTRAINT data_source_groups_group_id_fkey FOREIGN KEY (group_id) REFERENCES public.groups(id);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='data_sources_org_id_fkey') = 0
THEN
ALTER TABLE ONLY public.data_sources
    ADD CONSTRAINT data_sources_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.organizations(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='events_org_id_fkey') = 0
THEN
ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.organizations(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='events_user_id_fkey') = 0
THEN
ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='favorites_org_id_fkey') = 0
THEN
ALTER TABLE ONLY public.favorites
    ADD CONSTRAINT favorites_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.organizations(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='favorites_user_id_fkey') = 0
THEN
ALTER TABLE ONLY public.favorites
    ADD CONSTRAINT favorites_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='groups_org_id_fkey') = 0
THEN
ALTER TABLE ONLY public.groups
    ADD CONSTRAINT groups_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.organizations(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='notification_destinations_org_id_fkey') = 0
THEN
ALTER TABLE ONLY public.notification_destinations
    ADD CONSTRAINT notification_destinations_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.organizations(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='notification_destinations_user_id_fkey') = 0
THEN
ALTER TABLE ONLY public.notification_destinations
    ADD CONSTRAINT notification_destinations_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='queries_data_source_id_fkey') = 0
THEN
ALTER TABLE ONLY public.queries
    ADD CONSTRAINT queries_data_source_id_fkey FOREIGN KEY (data_source_id) REFERENCES public.data_sources(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='queries_last_modified_by_id_fkey') = 0
THEN
ALTER TABLE ONLY public.queries
    ADD CONSTRAINT queries_last_modified_by_id_fkey FOREIGN KEY (last_modified_by_id) REFERENCES public.users(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='queries_latest_query_data_id_fkey') = 0
THEN
ALTER TABLE ONLY public.queries
    ADD CONSTRAINT queries_latest_query_data_id_fkey FOREIGN KEY (latest_query_data_id) REFERENCES public.query_results(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='queries_org_id_fkey') = 0
THEN
ALTER TABLE ONLY public.queries
    ADD CONSTRAINT queries_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.organizations(id);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='queries_user_id_fkey') = 0
THEN
ALTER TABLE ONLY public.queries
    ADD CONSTRAINT queries_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='queries_user_id_fkey') = 0
THEN
ALTER TABLE ONLY public.query_results
    ADD CONSTRAINT query_results_data_source_id_fkey FOREIGN KEY (data_source_id) REFERENCES public.data_sources(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='queries_user_id_fkey') = 0
THEN
ALTER TABLE ONLY public.query_results
    ADD CONSTRAINT query_results_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.organizations(id);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='query_snippets_org_id_fkey') = 0
THEN
ALTER TABLE ONLY public.query_snippets
    ADD CONSTRAINT query_snippets_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.organizations(id);
END IF;
END
$do$;



DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='query_snippets_user_id_fkey') = 0
THEN
ALTER TABLE ONLY public.query_snippets
    ADD CONSTRAINT query_snippets_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='users_org_id_fkey') = 0
THEN
ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.organizations(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='visualizations_query_id_fkey') = 0
THEN
ALTER TABLE ONLY public.visualizations
    ADD CONSTRAINT visualizations_query_id_fkey FOREIGN KEY (query_id) REFERENCES public.queries(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='widgets_dashboard_id_fkey') = 0
THEN
ALTER TABLE ONLY public.widgets
    ADD CONSTRAINT widgets_dashboard_id_fkey FOREIGN KEY (dashboard_id) REFERENCES public.dashboards(id);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_constraint where conname='widgets_visualization_id_fkey') = 0
THEN
ALTER TABLE ONLY public.widgets
    ADD CONSTRAINT widgets_visualization_id_fkey FOREIGN KEY (visualization_id) REFERENCES public.visualizations(id);
END IF;
END
$do$;
DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'enums') = 0
THEN
	CREATE TABLE public.enums (
    id serial,
    org_id integer NOT NULL,
    code character varying(100) NOT NULL,
    name character varying(255) NOT NULL,
    is_builtin boolean default false,
    items  json[] DEFAULT '{}'::json[]
	);
	
   ALTER TABLE public.enums
    ADD CONSTRAINT enums_pkey PRIMARY KEY(id);
    ADD CONSTRAINT enums_org_id_fkey FOREIGN KEY (org_id)
      REFERENCES public.organizations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;    
     CREATE unique INDEX enums_org_code ON public.enums USING btree (org_id,code);
    
   insert into public.enums(code,name,is_builtin,items,org_id)
select   'calendar_enum','时间结构类型',true,array[
'{"key":"nature","value":"nature","label":"自然日历","title":"nature 自然日历"}'::json,
'{"key":"account","value":"account","label":"会计日历","title":"account 会计日历"}'::json,
'{"key":"work","value":"work","label":"生产日历","title":"work 生产日历"}'::json],id From public.organizations where id not in(select org_id from public.enums where code='calendar_enum');
   insert into public.enums(code,name,is_builtin,items,org_id)
select   'timeserialnode_enum','时间节点类型',true,array[
'{"key":"year","value":"year","label":"自然年","title":"year 自然年"}'::json,
'{"key":"half","value":"half","label":"半年","title":"half 半年"}'::json,
'{"key":"quarter","value":"quarter","label":"自然季度","title":"quarter 自然季度"}'::json,
'{"key":"month","value":"month","label":"自然月","title":"month 自然月"}'::json,
'{"key":"week","value":"week","label":"自然周","title":"week 自然周"}'::json,
'{"key":"day","value":"day","label":"自然周","title":"day 自然日"}'::json,
'{"key":"daterange","value":"daterange","label":"日期范围","title":"daterange 日期范围"}'::json,
'{"key":"datetimerange","value":"datetimerange","label":"日期时间范围","title":"datetimerange 日期时间范围"}'::json],id 
From public.organizations where id not in(select org_id from public.enums where code='timeserialnode_enum');
   insert into public.enums(code,name,is_builtin,items,org_id)
select  'timeserialstatus_enum','时间节点状态',true,array[
'{"key":"draft","value":"draft","label":"未打开","title":"draft 未打开"}'::json,
'{"key":"active","value":"active","label":"打开","title":"active 打开"}'::json,
'{"key":"freeze","value":"freeze","label":"冻结","title":"freeze 冻结"}'::json,
'{"key":"publish","value":"publish","label":"发布","title":"publish 发布"}'::json,
'{"key":"archive","value":"archive","label":"归档","title":"archive 归档"}'::json],id From public.organizations where id not in(select org_id from public.enums where code='timeserialstatus_enum');
   insert into public.enums(code,name,is_builtin,items,org_id)
select   'orgunit_enum','部门类型',true,array[
'{"key":"enterprise","value":"enterprise","label":"企业集团","title":"enterprise 企业集团"}'::json,
'{"key":"company","value":"company","label":"公司","title":"company 公司"}'::json,
'{"key":"branch","value":"branch","label":"分子公司","title":"branch 分子公司"}'::json,
'{"key":"group","value":"group","label":"事业部","title":"group 事业部"}'::json,
'{"key":"dept","value":"dept","label":"部门","title":"dept 部门"}'::json,
'{"key":"team","value":"team","label":"班组","title":"team 班组"}'::json,
'{"key":"admin","value":"admin","label":"管理部门","title":"admin 管理部门"}'::json,
'{"key":"build","value":"build","label":"生产部门","title":"build 生产部门"}'::json,
'{"key":"sale","value":"sale","label":"销售部门","title":"sale 销售部门"}'::json,
'{"key":"logic","value":"logic","label":"物流部门","title":"logic 物流部门"}'::json],id From public.organizations where id not in(select org_id from public.enums where code='orgunit_enum');

--select   'orgunit_enum','部门类型',true,array[
--'{"key":"clinical","value":"clinical","label":"临床医疗","title":"clinical 临床医疗"}'::json,
--'{"key":"technology","value":"technology","label":"医疗技术","title":"technology 医疗技术"}'::json,
--'{"key":"assistance","value":"assistance","label":"医疗辅助","title":"assistance 医疗辅助"}'::json,
--'{"key":"logistics","value":"logistics","label":"行政后勤","title":"logistics 行政后勤"}'::json,
--'{"key":"dept","value":"dept","label":"部门","title":"dept 部门"}'::json],id From public.organizations where id not in(select org_id from public.enums where code='orgunit_enum');
   insert into public.enums(code,name,is_builtin,items,org_id)
select  'orgstruct_enum','部门类型',true,array[
'{"key":"admin","value":"admin","label":"行政组织","title":"admin 行政组织"}'::json,
'{"key":"hr","value":"hr","label":"人事组织","title":"hr 人事组织"}'::json,
'{"key":"build","value":"build","label":"生产组织","title":"build 生产组织"}'::json],id From public.organizations where id not in(select org_id from public.enums where code='orgstruct_enum');

insert into public.enums(code,name,is_builtin,items,org_id)
select   'menustyle_enum','菜单样式',true,array[
'{"key":"horizontal ","value":"horizontal","label":"顶部水平菜单","title":"horizontal 顶部水平菜单"}'::json,
'{"key":"vertical","value":"vertical","label":"左侧垂直菜单","title":"vertical 左侧垂直菜单"}'::json],id 
From public.organizations where id not in(select org_id from public.enums where code='menustyle_enum');
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'timestruct') = 0 
THEN
	CREATE TABLE public.timestruct (
    id serial,
    org_id integer NOT NULL,
    calendar_enum character varying(100) NOT NULL,
    title character varying(255) NOT NULL,
    timeserialnode_enum character varying(100) NOT NULL,
    num integer default 0,
    parent_id integer NOT NULL default 0 
	);
	   ALTER TABLE public.timestruct
    ADD CONSTRAINT timestruct_pkey PRIMARY KEY(id);

    ADD CONSTRAINT timestruct_org_id_fkey FOREIGN KEY (org_id)
      REFERENCES public.organizations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;    
    CREATE unique INDEX timestruct_org_calendar_code ON public.timestruct USING btree (org_id,calendar_enum,title);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'timeserial') = 0 
THEN
	CREATE TABLE public.timeserial (
    id serial,
    org_id integer NOT NULL,
    num integer NOT NULL,
    calendar_enum character varying(100) NOT NULL,
    timestruct_id integer NOT NULL,
    parent_id  integer NOT NULL default 0,
    name character varying(255) NOT NULL,
    begin_date  timestamp with time zone NOT NULL,
    close_date  timestamp with time zone NOT NULL,
    timeserialstatus_enum character varying(100) NOT NULL default 'draft',
    updated_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone NOT NULL 
	);
	   ALTER TABLE public.timeserial
    ADD CONSTRAINT timeserial_pkey PRIMARY KEY(id);

    ADD CONSTRAINT timeserial_org_id_fkey FOREIGN KEY (org_id)
      REFERENCES public.organizations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;    
    CREATE unique INDEX timeserial_org_calendar_timestruct_parent_num ON public.timeserial USING btree (org_id,calendar_enum,timestruct_id,parent_id,num);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'query_timeserial') = 0 
THEN
	CREATE TABLE public.query_timeserial (
    id serial,
    query_id integer NOT NULL,
    org_id integer NOT NULL,
    timeserial_id integer NOT NULL,
    query text NULL,
    query_hash character varying(32) NULL,    
    latest_query_data_id integer,
    is_draft boolean NOT NULL,
    schedule_failures integer default 0
	);
	   ALTER TABLE public.query_timeserial
    ADD CONSTRAINT query_timeserial_pkey PRIMARY KEY(id);

    ADD CONSTRAINT query_timeserial_org_id_fkey FOREIGN KEY (org_id)
      REFERENCES public.organizations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION; 
    CREATE unique INDEX query_id_org_timeserial_id ON public.query_timeserial USING btree (query_id,org_id,timeserial_id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'orgunit') = 0
THEN
	CREATE TABLE public.orgunit (
    id serial,
    org_id integer NOT NULL,
    code character varying(100) NOT NULL,
    name character varying(255) NOT NULL,
    orgunit_enums character varying(100)[] NOT NULL,
    shortname  character varying(100) NULL default '',
    is_active boolean default true,
    updated_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone NOT NULL
	);
     ALTER TABLE public.orgunit
    ADD CONSTRAINT orgunit_pkey PRIMARY KEY(id);
     ALTER TABLE public.orgunit
    ADD CONSTRAINT orgunit_org_id_fkey FOREIGN KEY (org_id)
      REFERENCES public.organizations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;    
     CREATE unique INDEX orgunit_org_code ON public.orgunit USING btree (org_id,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'orgstruct') = 0 
THEN
	CREATE TABLE public.orgstruct (
    id serial,
    org_id integer NOT NULL,
    orgstruct_enum character varying(100) NOT NULL,
    effect_date date NOT NULL,
    expire_date date,
    details json[] DEFAULT '{}'::json[],    
    updated_at timestamp with time zone NOT NULL,
    created_at timestamp with time zone NOT NULL
	);
	   ALTER TABLE public.orgstruct
    ADD CONSTRAINT orgstruct_pkey PRIMARY KEY(id);
     ALTER TABLE public.orgstruct
    ADD CONSTRAINT orgstruct_org_id_fkey FOREIGN KEY (org_id)
      REFERENCES public.organizations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;
     CREATE unique INDEX orgstruct_org_orgstruct_effect ON public.orgstruct USING btree (org_id,orgstruct_enum,effect_date);          
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'kpis') = 0
THEN
    CREATE TABLE public.kpis (
    id serial,
    org_id integer NOT NULL,
    kpi_enum character varying(100) NOT NULL,
    code character varying(100) NOT NULL,
    name character varying(255) NOT NULL,
    orgstruct_enum character varying(100),		
    key_enums character varying(100),    		
    query text NOT NULL,					
    orgcascade_enum character varying(100),   		
    orgquery_id integer,   							
    orgquery_column_id character varying(100),   	
    orgapportion_enum character varying(100),   	
    enumcascade_enum character varying(100),   		
    enumquery_id integer,   
    enumquery_column_id character varying(100),   
    enumapportion_enum character varying(100)
	);
	
   ALTER TABLE public.kpis ADD CONSTRAINT kpis_pkey PRIMARY KEY(id);
    ALTER TABLE public.kpis
    ADD CONSTRAINT kpis_org_id_fkey FOREIGN KEY (org_id)
      REFERENCES public.organizations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION; 
   CREATE unique INDEX kpis_enum_org_name ON public.kpis USING btree (org_id,kpi_enum,name);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'kpi_results') = 0
THEN
    CREATE TABLE public.kpi_results (
    id serial,
    org_id integer NOT NULL,
    kpi_id integer not null,
    timeserial_id integer not null,
    orgunit_code character varying(100),
    key_enum character varying(100),
    result numeric default 0,			
    cascade numeric default 0,			
    total numeric default 0,			
    details json DEFAULT '{}'::json		
    );
	
   ALTER TABLE public.kpi_results ADD CONSTRAINT kpi_results_pkey PRIMARY KEY(id);
   CREATE unique INDEX kpi_results_org_kpi_timeserial_orgunit_otherkey ON public.kpi_results USING btree (org_id,kpi_id,timeserial_id,orgunit_code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'dashboard_groups') = 0 
THEN
    CREATE TABLE public.dashboard_groups (
    id serial,
    dashboard_id integer NOT NULL,
    group_id integer NOT NULL
    );
	
    ALTER TABLE ONLY public.dashboard_groups ADD CONSTRAINT dashboard_groups_pkey PRIMARY KEY (id);
    ALTER TABLE ONLY public.dashboard_groups ADD CONSTRAINT dashboard_groups_dashboard_id_fkey FOREIGN KEY (dashboard_id) REFERENCES public.dashboards(id);
    ALTER TABLE ONLY public.dashboard_groups ADD CONSTRAINT dashboard_groups_group_id_fkey FOREIGN KEY (group_id) REFERENCES public.groups(id);
    CREATE UNIQUE INDEX dashboard_groups_dashboard_group ON public.dashboard_groups USING btree (dashboard_id, group_id);
	
END IF;
END
$do$;


ALTER FUNCTION public.queries_search_vector_update() OWNER TO redash;
ALTER TABLE public.access_permissions OWNER TO redash;
ALTER TABLE public.alembic_version OWNER TO redash;
ALTER TABLE public.alert_subscriptions OWNER TO redash;
ALTER TABLE public.alerts OWNER TO redash;
ALTER TABLE public.api_keys OWNER TO redash;
ALTER TABLE public.changes OWNER TO redash;
ALTER TABLE public.dashboards OWNER TO redash;
ALTER TABLE public.data_source_groups OWNER TO redash;
ALTER TABLE public.data_sources OWNER TO redash;
ALTER TABLE public.events OWNER TO redash;
ALTER TABLE public.favorites OWNER TO redash;
ALTER TABLE public.groups OWNER TO redash;
ALTER TABLE public.notification_destinations OWNER TO redash;
ALTER TABLE public.organizations OWNER TO redash;
ALTER TABLE public.queries OWNER TO redash;
ALTER TABLE public.query_results OWNER TO redash;
ALTER TABLE public.query_snippets OWNER TO redash;
ALTER TABLE public.users OWNER TO redash;
ALTER TABLE public.visualizations OWNER TO redash;
ALTER TABLE public.widgets OWNER TO redash;




ALTER TABLE public.enums OWNER TO  redash;
ALTER TABLE public.timestruct OWNER TO  redash;
ALTER TABLE public.timeserial OWNER TO  redash;
ALTER TABLE public.query_timeserial OWNER TO  redash;
ALTER TABLE public.orgunit OWNER TO  redash;
ALTER TABLE public.orgstruct OWNER TO  redash;
ALTER TABLE public.kpis OWNER TO  redash;
ALTER TABLE public.kpi_results OWNER TO  redash;
ALTER TABLE public.dashboard_groups OWNER TO  redash;
------------------20200419合同管理------------------------
DO
$do$
BEGIN
IF (select  count(*) cc from public.enums where code='bill_enum') = 0 
THEN
   insert into public.enums(code,name,items,is_builtin,org_id)
select   'bill_enum','单据类型',array['{"key":"contractapply","value":"contractapply","label":"合同申请单","title":"contractapply 合同申请单"}'::json,
'{"key":"contracts","value":"contracts","label":"合同","title":"contracts 合同"}'::json,
'{"key":"contractseal","value":"contractseal","label":"合同用印申请单","title":"contractseal 合同用印申请单"}'::json,
'{"key":"contractrecord","value":"contractrecord","label":"合同备案申请单","title":"contractrecord 合同备案申请单"}'::json,
'{"key":"contractchange","value":"contractchange","label":"合同变更申请单","title":"contractchange 合同变更申请单"}'::json,
'{"key":"contractprogress","value":"contractprogress","label":"合同违约登记单","title":"contractprogress 合同违约登记单"}'::json,
'{"key":"contractstop","value":"contractstop","label":"合同终止申请单","title":"contractstop 合同终止申请单"}'::json,
'{"key":"contractfinish","value":"contractfinish","label":"合同验收申请单","title":"contractfinish 合同验收申请单"}'::json,
'{"key":"contractsuspend","value":"contractsuspend","label":"合同中止申请单","title":"contractsuspend 合同中止申请单"}'::json,
'{"key":"contractsettle","value":"contractsettle","label":"合同付款单","title":"contractsettle 合同付款单"}'::json],true,id 
From public.organizations where id not in(select org_id from public.enums where code='bill_enum');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.enums where code='empowerduties_enum') = 0 
THEN
   insert into public.enums(code,name,items,is_builtin,org_id)
select   'empowerduties_enum','职务',array['{"key":"总会计师","value":"总会计师","label":"总会计师","title":"contractapply 总会计师"}'::json],true,id 
From public.organizations where id not in(select org_id from public.enums where code='empowerduties_enum');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.enums where code='amount_enum') = 0 
THEN
   insert into public.enums(code,name,items,is_builtin,org_id)
select   'amount_enum','款项类型',array['{"key":"yfk","value":"yfk","label":"预付款","title":"yfk 预付款"}'::json,
'{"key":"jdk","value":"jdk","label":"进度款","title":"jdk 进度款"}'::json,
'{"key":"wk","value":"wk","label":"尾款","title":"wk 尾款"}'::json,
'{"key":"ysk","value":"ysk","label":"预收款","title":"ysk 预收款"}'::json],true,id From public.organizations where id not in(select org_id from public.enums where code='amount_enum');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.enums where code='contract_enum') = 0 
THEN
   insert into public.enums(code,name,items,is_builtin,org_id)
select   'contract_enum','合同类别',
array['{"key":"sheb","value":"sheb","label":"设备","title":"设备","children":[{"key":"yizb1","value":"yizb1","label":"医装部","title":"医装部"},
{"key":"houbb1","value":"houbb1","label":"后保部","title":"后保部"},{"key":"jijb1","value":"jijb1","label":"基建部","title":"基建部"},
{"key":"xinxb1","value":"xinxb1","label":"信息部","title":"信息部"}]}'::json,
'{"key":"gongc","value":"gongc","label":"工程","title":"工程","children":[{"key":"houbb2","value":"houbb2","label":"后保部","title":"后保部"},
{"key":"jijb2","value":"jijb2","label":"基建部","title":"基建部"}]}'::json,
'{"key":"gongcfw","value":"gongcfw","label":"工程服务","title":"工程服务","children":[{"key":"houbb3","value":"houbb3","label":"后保部","title":"后保部"},
{"key":"jijb3","value":"jijb3","label":"基建部","title":"基建部"}]}'::json,
'{"key":"houqwz","value":"houqwz","label":"后勤物资","title":"后勤物资","children":[{"key":"houbb4","value":"houbb4","label":"后保部","title":"后保部"}]}'::json,
'{"key":"yilwz","value":"yilwz","label":"医疗物资","title":"医疗物资","children":[{"key":"yizb5","value":"yizb5","label":"医装部","title":"医装部"}]}'::json,
'{"key":"yaop","value":"yaop","label":"药品","title":"药品","children":[{"key":"yaojk6","value":"yaojk6","label":"药剂科","title":"药剂科"}]}'::json,
'{"key":"xiaodp","value":"xiaodp","label":"消毒品","title":"消毒品","children":[{"key":"yaojk7","value":"yaojk7","label":"药剂科","title":"药剂科"}]}'::json,
'{"key":"shij","value":"shij","label":"试剂","title":"试剂","children":[{"key":"yizb8","value":"yizb8","label":"医装部","title":"医装部"}]}'::json,
'{"key":"yiyhc","value":"yiyhc","label":"医用耗材","title":"医用耗材","children":[{"key":"yizb9","value":"yizb9","label":"医装部","title":"医装部"}]}'::json,
'{"key":"houqfw","value":"houqfw","label":"后勤服务","title":"后勤服务","children":[{"key":"houbb10","value":"houbb10","label":"后保部","title":"后保部"}]}'::json,
'{"key":"shenjfw","value":"shenjfw","label":"审计服务","title":"审计服务","children":[{"key":"shenjb11","value":"shenjb11","label":"审计部","title":"审计部"}]}'::json,
'{"key":"caiwfw","value":"caiwfw","label":"财务服务","title":"财务服务","children":[{"key":"caiwb12","value":"caiwb12","label":"财务部","title":"财务部"}]}'::json,
'{"key":"chengz","value":"chengz","label":"承租","title":"承租","children":[{"key":"houbb13","value":"houbb13","label":"后保部","title":"后保部"}]}'::json,
'{"key":"chuz","value":"chuz","label":"出租","title":"出租","children":[{"key":"houbb14","value":"houbb14","label":"后保部","title":"后保部"}]}'::json,
'{"key":"xuanc","value":"xuanc","label":"宣传","title":"宣传","children":[{"key":"yuanwb15","value":"yuanwb15","label":"院务部","title":"院务部"}]}'::json,
'{"key":"feixc","value":"feixc","label":"非宣传","title":"非宣传","children":[{"key":"yuanwb21","value":"yuanwb21","label":"院务部","title":"院务部"}]}'::json,
'{"key":"peix","value":"peix","label":"培训","title":"培训","children":[{"key":"kejb16","value":"kejb16","label":"科教部","title":"科教部"}]}'::json,
'{"key":"gonghcg","value":"gonghcg","label":"工会采购","title":"工会采购","children":[{"key":"gongh17","value":"gongh17","label":"工会","title":"工会"}]}'::json,
'{"key":"weix","value":"weix","label":"维修","title":"维修","children":[{"key":"yizb18","value":"yizb18","label":"医装部","title":"医装部"},
{"key":"houbb18","value":"houbb18","label":"后保部","title":"后保部"},{"key":"jijb18","value":"jijb18","label":"基建部","title":"基建部"},
{"key":"xinxb18","value":"xinxb18","label":"信息部","title":"信息部"}]}'::json,
'{"key":"weib","value":"weib","label":"维保","title":"维保","children":[{"key":"yizb19","value":"yizb19","label":"医装部","title":"医装部"},
{"key":"houbb19","value":"houbb19","label":"后保部","title":"后保部"},{"key":"jijb19","value":"jijb19","label":"基建部","title":"基建部"},
{"key":"xinxb19","value":"xinxb19","label":"信息部","title":"信息部"}]}'::json,
'{"key":"xinxfw","value":"xinxfw","label":"信息服务","title":"信息服务","children":[{"key":"xinxb22","value":"xinxb22","label":"信息部","title":"信息部"}]}'::json,
'{"key":"qit","value":"qit","label":"其他","title":"其他","children":[{"key":"qitbm20","value":"qitbm20","label":"其他部门","title":"其他部门"}]}'::json],true,id 
From public.organizations where id not in(select org_id from public.enums where code='contract_enum');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'workflow') = 0
THEN
	CREATE TABLE public.workflow
	(
	  id serial,
	  org_id integer NOT NULL,
      updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
      created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  userid  integer NOT NULL,
	  name character varying(140),
	  bill_enum character varying(140),
	  bill_type integer NOT NULL DEFAULT 0,
	  is_active boolean default false,
	  orgunitid integer default 0
	);
   ALTER TABLE public.workflow ADD CONSTRAINT workflow_pkey PRIMARY KEY(id);
   CREATE unique INDEX workflow_org_bill_enum_name_bill_type_orgunitid ON public.workflow USING btree (org_id,bill_enum,name,bill_type,orgunitid);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'workflow_action') = 0
THEN
CREATE TABLE public.workflow_action
	(
	  id serial,
	  org_id integer NOT NULL,
      updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
      created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  userid  integer NOT NULL,
	  workflowid integer NOT NULL,
	  billid integer NOT NULL,
	  bill_enum character varying(140),
	  bill_type integer NOT NULL DEFAULT 0,
	  workflow_bill_stateid integer NOT NULL,--单据流程ID
	  workflow_stateid integer NOT NULL,
	  completed_by  integer NOT NULL,
	  status   integer NOT NULL DEFAULT 0,--0待审核，1-驳回，2-已审核
	  comments text
	);
   ALTER TABLE public.workflow_action ADD CONSTRAINT workflow_action_pkey PRIMARY KEY(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'workflow_bill_state') = 0
THEN
	CREATE TABLE public.workflow_bill_state
	(
	  id serial,
	  org_id integer NOT NULL,
      updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
      created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  userid  integer NOT NULL,
	  docstatus integer  DEFAULT 1 NOT NULL, ---0开始，1 中间审批流程，2审批结束
	  workflowid integer NOT NULL,--流程ID
	  workflow_stateid integer NOT NULL,--状态ID
	  billid integer NOT NULL,--单据ID
	  bill_enum character varying(140),--单据
	  bill_type integer NOT NULL DEFAULT 0,--单据类型
	  allow_user integer[] NULL,--审核人
	  allow_group integer[] NULL,--审核组
	  allow_col   character varying(255)[] NULL,--允许编辑字段
	  is_active boolean default true,--本次流程是否结束
	  message text
	);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'workflow_state') = 0
THEN
	CREATE TABLE public.workflow_state
	(
	  id serial,
	  org_id integer NOT NULL,
	  docstatus integer  DEFAULT 1 NOT NULL, ---0开始，1 中间审批流程，2审批结束
      updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
      created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  userid  integer NOT NULL,
	  name character varying(140)
	);
   ALTER TABLE public.workflow_state ADD CONSTRAINT workflow_state_pkey PRIMARY KEY(id);
   CREATE unique INDEX workflow_state_org_name ON public.workflow_state USING btree (org_id,name);

	insert into public.workflow_state(docstatus,userid,name,org_id)
	select 0,0,'开始',id
	From public.organizations where id not in(select org_id from public.workflow_state where name='开始提交');
	insert into public.workflow_state(docstatus,userid,name,org_id)
	select 1,0,'主管批准',id
	From public.organizations where id not in(select org_id from public.workflow_state where name='主管批准');
	insert into public.workflow_state(docstatus,userid,name,org_id)
	select 2,0,'结束',id
	From public.organizations where id not in(select org_id from public.workflow_state where name='流程结束');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'workflow_transition') = 0
THEN
	CREATE TABLE public.workflow_transition
	(
	  id serial,
	  org_id integer NOT NULL,
      updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
      created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  userid  integer NOT NULL,
	  workflowid integer NOT NULL,
	  workflow_stateid integer NOT NULL,
	  next_type integer DEFAULT 0 NOT NULL,--0审核通过，1 驳回
	  next_workflow_stateid integer NOT NULL,--下一状态
	  allow_user integer[] NULL,--审核人
	  allow_group integer[] NULL,--审核组
	  allow_col   character varying(255)[] NULL,--允许编辑字段
	  condition text
	);
   ALTER TABLE public.workflow_transition ADD CONSTRAINT workflow_transition_pkey PRIMARY KEY(id);
   CREATE unique INDEX workflow_transition_org_workflowid_workflow_stateid ON public.workflow_transition USING btree (org_id,workflowid,workflow_stateid,next_type,next_workflow_stateid);
END IF;
END
$do$;
DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'tabletype') = 0 ---单据号设置表
THEN
	CREATE TABLE public.tabletype (
    id serial,
    org_id integer NOT NULL,
    code character varying(255) NOT NULL,--表名
    name character varying(255) NOT NULL,--标题
	type integer default 0 NULL,
    numauto boolean default true,--是否自动编号
    numhand boolean default false,--是否手动编号
    numhead character varying(255) NULL,--标志
    numdate character varying(255) NULL,--按日期YYYYMMDD
    numfoot integer default 1,--流水号默认从1开始
    footlenth integer default 4,--流水号长度
    updated_at timestamp with time zone NOT NULL,--最后修改日期
    created_at timestamp with time zone NOT NULL --新建日期
	);
	   ALTER TABLE public.tabletype
    ADD CONSTRAINT tabletype_pkey PRIMARY KEY(id);
    CREATE unique INDEX tabletype_org_code_name ON public.tabletype USING btree (org_id,code,name);

	insert into public.tabletype(code,name,
	    numauto,numhand,numhead,numdate,
	    numfoot,footlenth,updated_at,created_at,org_id)
	 select 'contractapply','合同申请单',false,true,'','YYYYMM',1,4,now(),now(),id 
	From public.organizations where id not in(select org_id from public.tabletype where code='contractapply')
	 union
	 select 'contracts','合同',true,false,'','YYYYMM',1,4,now(),now(),id 
	From public.organizations where id not in(select org_id from public.tabletype where code='contracts')
	 union
	 select 'contractseal','合同用印申请单',false,true,'','YYYYMM',1,4,now(),now(),id 
	From public.organizations where id not in(select org_id from public.tabletype where code='contractseal')
	 union
	 select 'contractrecord','合同备案申请单',false,true,'','YYYYMM',1,4,now(),now(),id 
	From public.organizations where id not in(select org_id from public.tabletype where code='contractrecord')
	 union
	 select 'contractchange','合同变更申请单',false,true,'','YYYYMM',1,4,now(),now(),id 
	From public.organizations where id not in(select org_id from public.tabletype where code='contractchange')
	 union
	 select 'contractprogress','合同违约登记单',false,true,'','YYYYMM',1,4,now(),now(),id 
	From public.organizations where id not in(select org_id from public.tabletype where code='contractprogress')
	 union
	 select 'contractstop','合同终止申请单',false,true,'','YYYYMM',1,4,now(),now(),id 
	From public.organizations where id not in(select org_id from public.tabletype where code='contractstop')
	 union
	 select 'contractfinish','合同验收申请单',false,true,'','YYYYMM',1,4,now(),now(),id 
	From public.organizations where id not in(select org_id from public.tabletype where code='contractfinish')
	 union
	 select 'contractsettle','合同付款单',false,true,'','YYYYMM',1,4,now(),now(),id 
	From public.organizations where id not in(select org_id from public.tabletype where code='contractsettle')
	union
	select 'customertemp','合同单位',false,true,'','YYYYMM',1,4,now(),now(),id 
	From public.organizations where id not in(select org_id from public.tabletype where code='customertemp')
	union
	select 'templates','合同模板',false,true,'','YYYYMM',1,4,now(),now(),id 
	From public.organizations where id not in(select org_id from public.tabletype where code='templates');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'tablefield') = 0 ---字段表
THEN
    CREATE TABLE public.tablefield (
    id serial,
    tablename character varying(255) NOT NULL,--表名
    rownum integer default 1,--流水号默认从1开始
    code character varying(255) NOT NULL,
    name character varying(255) NOT NULL,
    is_activity integer default 1
    );
	   ALTER TABLE public.tablefield
    ADD CONSTRAINT tablefield_pkey PRIMARY KEY(id);
    CREATE unique INDEX tablefield_code_tablename ON public.tablefield USING btree (code,tablename);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'customertemp') = 0 --供应商表
THEN
create table public.customertemp
(
	  id serial,--ID
	  org_id integer NOT NULL,
	  lngcustomerid               integer default 0 not null,--HRP供应商ID
	  strcustomercode             character varying(255) default ' ' not null,--供应商编码
	  strcustomername             character varying(255) default ' ' not null,----供应商名称
	  lngcustomertypeid           integer default 0 not null,--供应商类别ID
	  strcustomertypecode         character varying(255) default ' ' not null,--供应商类别编码
	  strcustomertypename         character varying(255) default ' ' not null,--供应商类别名称
	  representative	      character varying(50) default ' ' ,--法定代表人
	  mobile		      character varying(50) default ' ' ,--电话
	  address		      character varying(500) default ' ',--地址
	  blnisinactive               boolean DEFAULT true,
	  bytstatus                 integer default 0 not null,---审核状态:0 未审批、1 审批中、2 已审批、3 删除
	  blnchuli                 integer default 0 not null,--处理标志  
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL--创建日期
);
 ALTER TABLE public.customertemp
    ADD CONSTRAINT customertemp_pkey PRIMARY KEY(id);
    CREATE unique INDEX customertemp_org_code ON public.customertemp USING btree (org_id,strcustomercode);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'employeetemp') = 0 --HRP职员表
THEN
create table public.employeetemp
(
  id serial,--ID
  org_id integer NOT NULL,
  lngemployeeid               integer default 0 not null,--职员ID
  stremployeecode             character varying(255) default ' ' not null,--职员编码
  stremployeename             character varying(255) default ' ' not null,----职员名称
  blnisinactive               integer default 0 not null,
  bytstatus                 integer default 0 not null,---审核状态:0 未审批、1 审批中、2 已审批、3 删除
  blnchuli                 integer default 0 not null,--处理标志  
  strlastdate              timestamp with time zone default now()   --最后更新时间
);
 ALTER TABLE public.employeetemp
    ADD CONSTRAINT employeetemp_pkey PRIMARY KEY(id);
    CREATE unique INDEX employeetemp_org_code ON public.employeetemp USING btree (org_id,stremployeecode);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contractapply') = 0 --合同申请单
THEN
	create table public.contractapply(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid  integer NOT NULL,--操作员ID
	  bill_date timestamp with time zone NOT NULL,--申请日期
	  employeeid integer NOT NULL,--经办人
	  departmentid  integer NOT NULL,--经办部门
	  code character varying(255) NOT NULL,----合同编号
	  name character varying(255) NOT NULL,--合同名称
	  type character varying(255)[] NOT NULL,--合同类别
	  party_aid integer NOT NULL,--甲方ID
	  party_bid integer NOT NULL,--乙方
	  party_cid integer  NULL,--其它关联方
	  pay_direction integer NOT NULL DEFAULT 0,--收付方向 0收款,1付款,2非经济
	  pricetype integer NOT NULL DEFAULT 0,--价格类型 0固定价,1单价结算
	  sign_date timestamp with time zone  NULL,--合同签订日期
	  start_date timestamp with time zone  NULL,--合同开始日期
	  end_date timestamp with time zone  NULL,--合同终止日期
	  amount numeric(29,2) NOT NULL,--金额
	  amount_words character varying(255) NOT NULL,----合同金额(大写)
	  status integer NOT NULL DEFAULT '0',--状态
	  message text,--纪录事项
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL--创建日期
	);

	ALTER TABLE public.contractapply
    ADD CONSTRAINT contractapply_pkey PRIMARY KEY(id);
    CREATE unique INDEX contractapply_org_code ON public.contractapply USING btree (org_id,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contracts') = 0 --合同表
THEN
	create table public.contracts(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid  integer NOT NULL,--操作员ID
	  bill_date timestamp with time zone NOT NULL,--日期
	  employeeid integer NOT NULL,--经办人
	  departmentid  integer NOT NULL,--经办部门
	  code character varying(255) NOT NULL,----合同编号
	  name character varying(255) NOT NULL,--合同名称
	  type character varying(255)[] NOT NULL,--合同类别
	  contracttype character varying(255)[] NULL,--合同类型
	  party_aid integer NOT NULL,--甲方ID
	  party_bid integer NOT NULL,--乙方
	  party_cid integer  NULL,--其它关联方
	  pay_direction integer NOT NULL DEFAULT 0,--收付方向 0收款,1付款,2非经济
	  pricetype integer NOT NULL DEFAULT 0,--价格类型 0固定价,1单价结算
	  sign_date timestamp with time zone  NULL,--合同签订日期
	  start_date timestamp with time zone  NULL,--合同开始日期
	  end_date timestamp with time zone  NULL,--合同终止日期
	  warn_date timestamp with time zone NULL,--提醒日期
	  warn_message text null default '' ,----提醒内容
	  warn_check  text null default '' ,----审核提醒
	  warn_checkdate timestamp with time zone NULL,--截止审批日期
	  volumepurchase integer NULL default 0,----是否带量采购
	  amount numeric(29,2) NOT NULL,--金额
	  amount_words character varying(255) NOT NULL,----合同金额(大写)
	  status integer NOT NULL DEFAULT 0,--状态
	  budgetcode character varying(255) NULL,----预算编号
          officialcode character varying(255) NULL,----正式合同编号
	  templateid integer NOT NULL DEFAULT 0,----模板id
	  templatecontent text,----模板内容
	  contractsealid integer NULL DEFAULT 0,--合同用印申请单ID
	  contractrecordid integer NULL DEFAULT 0,--备案ID
	  billstatusid integer NULL DEFAULT 0,--0执行中,1中止,2验收,3终止
	  message text,--纪录事项
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  typeid integer  NULL default 0, --有来源单据类型0续签，1另签，2补签
	  infrastructure integer NULL default 0,--是否第三方
	  amounttype character varying(255)[] NULL, --资金类别
	  recordcode character varying(255)   NULL DEFAULT '',----备案号
	  puremployeeid integer NULL default 0,--采购经办人
	  sourcetype  character varying(255) NULL default '', --来源单据类型
	  zhengcai integer NULL default 0,--0非政采，1政采
	  isbulu integer NULL default 0,--0正常，1补录
	  sourceid  integer  NULL default 0 ,--来源单据ID
	  iszhaobiao integer default 0 NULL,--是否招标
	  empowerperson character varying(255) default '' NULL,--授权人
	  empowersex character varying(10) default '' NULL,--性别
	  empowerduties character varying(255) default '' NULL,--职务
	  agreementcount character varying(10) default '' NULL,--协议份数
	  warnsealuser character varying(555) default '' ---用印提醒人
	);

	ALTER TABLE public.contracts
    ADD CONSTRAINT contracts_pkey PRIMARY KEY(id);
    CREATE unique INDEX contracts_org_code ON public.contracts USING btree (org_id,code);
END IF;
END
$do$;


DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contractseal') = 0 --合同用印申请单
THEN
	create table public.contractseal(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid  integer NOT NULL,--操作员ID
	  bill_date timestamp with time zone NOT NULL,--日期
	  contractsid integer NOT NULL,--合同ID
	  code character varying(255) NOT NULL,----编号
	  name character varying(255) NOT NULL,--名称
	  departmentid  integer default 0,--经办部门
	  employeeid integer NULL,--院长办公室
	  sealdate timestamp with time zone NULL,--盖章时间
	  sealcount integer NULL DEFAULT '0',--盖章几份
	  message text,--纪录事项
	  status integer NOT NULL DEFAULT '0',--状态
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  sourcetype  character varying(255) NULL default '', --来源单据类型
	  sourceid  integer  NULL default 0, --来源单据ID
	  handledept integer NULL default 0,--经办人员
          archive boolean DEFAULT true ---是否归档
	);

	ALTER TABLE public.contractseal
    ADD CONSTRAINT contractseal_pkey PRIMARY KEY(id);
    CREATE unique INDEX contractseal_org_code ON public.contractseal USING btree (org_id,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contractrecord') = 0 --合同备案申请单
THEN
	create table public.contractrecord(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid  integer NOT NULL,--操作员ID
	  bill_date timestamp with time zone NOT NULL,--日期
	  contractsid integer NOT NULL,--合同ID
	  code character varying(255) NOT NULL,----编号
	  name character varying(255) NOT NULL,--名称
	  departmentid  integer default 0,--经办部门
	  message text,--纪录事项
	  status integer NOT NULL DEFAULT '0',--状态
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  sourcetype  character varying(255) NULL default '', --来源单据类型
	  sourceid  integer  NULL default 0 --来源单据ID
	);

	ALTER TABLE public.contractrecord
    ADD CONSTRAINT contractrecord_pkey PRIMARY KEY(id);
    CREATE unique INDEX contractrecord_org_code ON public.contractrecord USING btree (org_id,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contractchange') = 0 --合同变更申请单
THEN
	create table public.contractchange(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid  integer NOT NULL,--操作员ID
	  bill_date timestamp with time zone NOT NULL,--日期
	  contractsid integer NOT NULL,--合同ID
	  employeeid integer NOT NULL,--经办人
	  departmentid  integer NOT NULL,--经办部门
	  code character varying(255) NOT NULL,----编号
	  contractcode character varying(255) NOT NULL,----合同编号
	  name character varying(255) NOT NULL,--名称
	  type character varying(255)[] NOT NULL,--合同类别
	  party_aid integer NOT NULL,--甲方ID
	  party_bid integer NOT NULL,--乙方
	  party_cid integer  NULL,--其它关联方
	  pay_direction integer NOT NULL DEFAULT 0,--收付方向 0收款,1付款,2非经济
	  pricetype integer NOT NULL DEFAULT 0,--价格类型 0固定价,1单价结算
	  sign_date timestamp with time zone NULL,--合同签订日期
	  start_date timestamp with time zone  NULL,--合同开始日期
	  end_date timestamp with time zone  NULL,--合同终止日期
	  amount numeric(29,2) NOT NULL,--金额
	  amount_words character varying(255) NOT NULL,----合同金额(大写)
	  budgetcode character varying(255) NULL,----预算编号
          officialcode character varying(255)  NULL,----正式合同编号
	  templateid integer NOT NULL DEFAULT 0,----模板id
	  templatecontent text,----模板内容
	  message text,--纪录事项
	  status integer NOT NULL DEFAULT '0',--状态
	  changebefore json DEFAULT '{}'::json  NOT NULL,--变更前
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  sourcetype  character varying(255) NULL default '', --来源单据类型
	  sourceid  integer  NULL default 0 --来源单据ID
	);

	ALTER TABLE public.contractchange
    ADD CONSTRAINT contractchange_pkey PRIMARY KEY(id);
    CREATE unique INDEX contractchange_org_code ON public.contractchange USING btree (org_id,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contractprogress') = 0 --合同违约登记单
THEN
	create table public.contractprogress(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid  integer NOT NULL,--操作员ID
	  bill_date timestamp with time zone NOT NULL,--日期
	  contractsid integer NOT NULL,--合同ID
	  percent integer NOT NULL,--%
	  code character varying(255) NOT NULL,----编号
	  name character varying(255) NOT NULL,--名称
	  departmentid  integer default 0,--经办部门
	  message text,--纪录事项
	  status integer NOT NULL DEFAULT '0',--状态
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  sourcetype  character varying(255) NULL default '', --来源单据类型
	  sourceid  integer  NULL default 0 --来源单据ID
	);

	ALTER TABLE public.contractprogress
    ADD CONSTRAINT contractprogress_pkey PRIMARY KEY(id);
    CREATE unique INDEX contractprogress_org_code ON public.contractprogress USING btree (org_id,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contractsettle') = 0 --合同付款单
THEN
	create table public.contractsettle(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid  integer NOT NULL,--操作员ID
	  bill_date timestamp with time zone NOT NULL,--日期
	  contractsid integer NOT NULL,--合同ID
	  contractprogressid integer  NULL,--违约登记单ID
	  percent integer  NULL,--%
	  code character varying(255) NOT NULL,----编号
	  name character varying(255) NOT NULL,--名称
	  departmentid  integer default 0,--经办部门
	  message text,--纪录事项
	  status integer NOT NULL DEFAULT '0',--状态
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  sourcetype  character varying(255) NULL default '', --来源单据类型
	  sourceid  integer  NULL default 0 --来源单据ID
	);

	ALTER TABLE public.contractsettle
    ADD CONSTRAINT contractsettle_pkey PRIMARY KEY(id);
    CREATE unique INDEX contractsettle_org_code ON public.contractsettle USING btree (org_id,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contractstop') = 0 --合同终止申请单
THEN
	create table public.contractstop(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid  integer NOT NULL,--操作员ID
	  bill_date timestamp with time zone NOT NULL,--日期
	  contractsid integer NOT NULL,--合同ID
	  code character varying(255) NOT NULL,----编号
	  name character varying(255) NOT NULL,--名称
	  departmentid  integer default 0,--经办部门
	  message text,--纪录事项
	  status integer NOT NULL DEFAULT '0',--状态
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  sourcetype  character varying(255) NULL default '', --来源单据类型
	  sourceid  integer  NULL default 0 --来源单据ID
	);

	ALTER TABLE public.contractstop
    ADD CONSTRAINT contractstop_pkey PRIMARY KEY(id);
    CREATE unique INDEX contractstop_org_code ON public.contractstop USING btree (org_id,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contractfinish') = 0 --合同验收申请单
THEN
	create table public.contractfinish(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid  integer NOT NULL,--操作员ID
	  bill_date timestamp with time zone NOT NULL,--日期
	  contractsid integer NOT NULL,--合同ID
	  code character varying(255) NOT NULL,----编号
	  name character varying(255) NOT NULL,--名称
	  departmentid  integer default 0,--经办部门
	  message text,--纪录事项
	  status integer NOT NULL DEFAULT '0',--状态
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  sourcetype  character varying(255) NULL default '', --来源单据类型
	  sourceid  integer  NULL default 0 --来源单据ID
	);

	ALTER TABLE public.contractfinish
    ADD CONSTRAINT contractfinish_pkey PRIMARY KEY(id);
    CREATE unique INDEX contractfinish_org_code ON public.contractfinish USING btree (org_id,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contractsuspend') = 0 --合同中止申请单
THEN
	create table public.contractsuspend(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid  integer NOT NULL,--操作员ID
	  bill_date timestamp with time zone NOT NULL,--日期
	  contractsid integer NOT NULL,--合同ID
	  code character varying(255) NOT NULL,----编号
	  name character varying(255) NOT NULL,--名称
	  departmentid  integer default 0,--经办部门
	  employeeid  integer default 0,--经办人
	  message text,--纪录事项
	  status integer NOT NULL DEFAULT '0',--状态
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  sourcetype  character varying(255) NULL default '', --来源单据类型
	  sourceid  integer  NULL default 0 --来源单据ID
	);

	ALTER TABLE public.contractsuspend
    ADD CONSTRAINT contractsuspend_pkey PRIMARY KEY(id);
    CREATE unique INDEX contractsuspend_org_code ON public.contractsuspend USING btree (org_id,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contractpayfor') = 0 --违约赔偿结算单
THEN
	create table public.contractpayfor(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid  integer NOT NULL,--操作员ID
	  bill_date timestamp with time zone NOT NULL,--日期
	  contractsid integer NOT NULL,--合同ID
	  code character varying(255) NOT NULL,----编号
	  name character varying(255) NOT NULL,--名称
	  departmentid  integer default 0,--经办部门
	  employeeid  integer default 0,--经办人
	  amount numeric(29,2) NOT NULL,--金额
	  message text,--纪录事项
	  status integer NOT NULL DEFAULT '0',--状态
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  sourcetype  character varying(255) NULL default '', --来源单据类型
	  sourceid  integer  NULL default 0 --来源单据ID
	);

	ALTER TABLE public.contractpayfor
    ADD CONSTRAINT contractpayfor_pkey PRIMARY KEY(id);
    CREATE unique INDEX contractpayfor_org_code ON public.contractpayfor USING btree (org_id,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contractactivity') = 0 --合同活动列表
THEN
	create table public.contractactivity(
	  id serial,--ID
	  userid  integer NOT NULL,--操作员ID
	  contractsid integer NOT NULL,--合同ID
	  message text,--纪录事项
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  sourcetype  character varying(255) not NULL default '', --来源单据类型
	  sourceid  integer not NULL default 0 --来源单据ID
	);

	ALTER TABLE public.contractactivity
    ADD CONSTRAINT contractactivity_pkey PRIMARY KEY(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'contractamount') = 0 --合同款项
THEN
	create table public.contractamount(
	  id serial,--ID
	  userid  integer NOT NULL,--操作员ID
	  bill_date timestamp with time zone NOT NULL,--日期
	  contractsid integer NOT NULL,--合同ID
	  amount_enum character varying(255) not NULL, --款项类型
	  amount numeric(29,2) NOT NULL default 0,--金额
	  finishamount numeric(29,2) NULL default 0,--完成金额
	  message text,--纪录事项
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  sourcetype  character varying(255)  NULL default '', --来源单据类型
	  sourceid  integer  NULL default 0 --来源单据ID
	);
	ALTER TABLE public.contractamount
    ADD CONSTRAINT contractamount_pkey PRIMARY KEY(id);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'attaches') = 0 --附件
THEN
	create table public.attaches(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid  integer NOT NULL,--操作员ID
	  type character varying(255) NOT NULL,----类型
	  annex json DEFAULT '{}'::json  NOT NULL,--附件
	  status integer NOT NULL DEFAULT '0',--状态
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  sourcetype  character varying(255)  not NULL default '', --来源单据类型
	  sourceid  integer not NULL default 0 --来源单据ID
	);

	ALTER TABLE public.attaches
    ADD CONSTRAINT attaches_pkey PRIMARY KEY(id);
    CREATE unique INDEX attaches_org_sourcetype_sourceid ON public.attaches USING btree (org_id,type,sourcetype,sourceid);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'templates') = 0 --模板
THEN
	CREATE TABLE public.templates
	(
	  id serial,--ID
	  org_id integer NOT NULL,
	  orgunit_id integer null default 0,
	  userid integer NOT NULL,
	  code character varying(255) NOT NULL,
	  name character varying(255) NOT NULL,
	  type character varying(255)[] NOT NULL,--合同类别
	  template text NOT NULL,
	  is_active boolean DEFAULT true,
	  updated_at timestamp with time zone NOT NULL DEFAULT now(),
	  created_at timestamp with time zone NOT NULL DEFAULT now()
	);

	ALTER TABLE public.templates
    ADD CONSTRAINT templates_pkey PRIMARY KEY(id);
    CREATE unique INDEX templates_org_code_type ON public.templates USING btree (org_id,code,type,orgunit_id);
	insert into public.templates(userid ,code ,name ,type,template,updated_at,created_at,org_id,orgunit_id)
	select 1 ,'1' ,'空白模板' ,array['kbmb'],
	'<div><textarea rows="10" cols="100" textAlignVertical="top" height="100px" value="" name ="contracttemplate0" id="contracttemplate0" class="contracttemplate" ></textarea></div>',
	now(),now(),id,0 
	From public.organizations where id not in(select org_id from public.templates where name='空白模板');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'globalsetting') = 0 --设置
THEN
	CREATE TABLE public.globalsetting
	(
	  id serial,--ID
	  org_id integer NOT NULL,
	  userid integer NOT NULL,---用户ID
	  type character varying(100) NOT NULL,----模块
	  code character varying(100) NOT NULL,----KEY
	  value character varying(500) NOT NULL,---值
	  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
	  created_at timestamp with time zone DEFAULT now() NOT NULL,--创建日期
	  message character varying(1000) NULL----说明
	);
	insert into public.globalsetting(userid,type,code,value,message,updated_at,created_at,org_id)
	select 1,'合同主页面','时间线显示日期','1','1_显示,0_不显示',now(),now(),id 
	From public.organizations where id not in(select org_id from public.globalsetting where type='合同主页面' and code='时间线显示日期');
	ALTER TABLE public.globalsetting
    ADD CONSTRAINT globalsetting_pkey PRIMARY KEY(id);
    CREATE unique INDEX globalsetting_org_type_code ON public.globalsetting USING btree (org_id,type,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from pg_tables where schemaname='public' and tablename = 'queryshowfield') = 0 --列表显示隐藏列
THEN
create table public.queryshowfield
(
  id serial,--ID
  org_id integer NOT NULL,
  code             character varying(255) default ' ' not null,--编码
  name             character varying(255) default ' ' not null,----名称
  fieldsshow   character varying(255)[] NULL,--允许编辑字段
  fieldsall json DEFAULT '{}'::json  NOT NULL,--字段
  message text,--备注
  userid           integer default 0 not null,--录入人ID
  updated_at timestamp with time zone DEFAULT now() NOT NULL,--最后修改日期
  created_at timestamp with time zone DEFAULT now() NOT NULL--创建日期
);
 ALTER TABLE public.queryshowfield
    ADD CONSTRAINT queryshowfield_pkey PRIMARY KEY(id);
    CREATE unique INDEX queryshowfield_org_code ON public.queryshowfield USING btree (org_id,code);
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.globalsetting where  type='主页' and code='视图url') = 0 
THEN
	insert into public.globalsetting(userid,type,code,value,message,updated_at,created_at,org_id)
	select 1,'主页','视图ID','0,0','查询ID，视图ID',now(),now(),id 
	From public.organizations where id not in(select org_id from public.globalsetting where  type='主页' and code='视图ID');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.globalsetting where  type='合同主页面' and code='款项url') = 0 
THEN
	insert into public.globalsetting(userid,type,code,value,message,updated_at,created_at,org_id)
	select 1,'合同主页面','款项ID','0,0','查询ID，视图ID.第一个参数是合同编码',now(),now(),id 
	From public.organizations where id not in(select org_id from public.globalsetting where  type='合同主页面' and code='款项ID');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.globalsetting where  type='合同页面' and code='预算控制') = 0 
THEN
	insert into public.globalsetting(userid,type,code,value,message,updated_at,created_at,org_id)
	select 1,'合同页面','预算控制','','[{合同类别编码,部门ID,输入,检查}]  输入(0_可空,1_必填,2_不可用),检查(0_不检查,1_提示,2_控制)',now(),now(),id 
	From public.organizations where id not in(select org_id from public.globalsetting where  type='合同页面' and code='预算控制');
END IF;
END
$do$;
-----
DO
$do$
BEGIN
IF (select  count(*) cc from public.enums where code='amounttype') = 0 
THEN
   insert into public.enums(code,name,items,is_builtin,org_id)
select   'amounttype','资金类别',array['{"key":"自有资金","value":"自有资金","label":"自有资金","title":"自有资金"}'::json,
'{"key":"财政资金","value":"财政资金","label":"财政资金","title":"财政资金"}'::json,
'{"key":"财政性资金","value":"财政性资金","label":"财政性资金","title":"财政性资金"}'::json,
'{"key":"工会资金","value":"工会资金","label":"工会资金","title":"工会资金"}'::json,
'{"key":"科研资金","value":"科研资金","label":"科研资金","title":"科研资金"}'::json,
'{"key":"党政资金","value":"党政资金","label":"党政资金","title":"党政资金"}'::json],true,id 
From public.organizations where id not in(select org_id from public.enums where code='amounttype');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.globalsetting where  type='主页' and code='操作日志') = 0 
THEN
	insert into public.globalsetting(userid,type,code,value,message,updated_at,created_at,org_id)
	select 1,'主页','操作日志','0,0','查询ID，视图ID',now(),now(),id 
	From public.organizations where id not in(select org_id from public.globalsetting where  type='主页' and code='操作日志');
END IF;
END
$do$;
DO
$do$
BEGIN
IF (select  count(*) cc from public.globalsetting where  type='主页' and code='职员') = 0 
THEN
	insert into public.globalsetting(userid,type,code,value,message,updated_at,created_at,org_id)
	select 1,'主页','职员','0,0','查询ID，视图ID',now(),now(),id 
	From public.organizations where id not in(select org_id from public.globalsetting where  type='主页' and code='职员');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.globalsetting where  type='主页' and code='部门') = 0 
THEN
	insert into public.globalsetting(userid,type,code,value,message,updated_at,created_at,org_id)
	select 1,'主页','部门','0,0','查询ID，视图ID',now(),now(),id 
	From public.organizations where id not in(select org_id from public.globalsetting where  type='主页' and code='部门');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.globalsetting where  type='主页' and code='合同单位') = 0 
THEN
	insert into public.globalsetting(userid,type,code,value,message,updated_at,created_at,org_id)
	select 1,'主页','合同单位','0,0','查询ID，视图ID',now(),now(),id 
	From public.organizations where id not in(select org_id from public.globalsetting where  type='主页' and code='合同单位');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.globalsetting where  type='合同新建' and code='采购经办人所属部门编码') = 0 
THEN
	insert into public.globalsetting(userid,type,code,value,message,updated_at,created_at,org_id)
	select 1,'合同新建','采购经办人所属部门编码','CGB','部门编码',now(),now(),id 
	From public.organizations where id not in(select org_id from public.globalsetting where  type='合同新建' and code='采购经办人所属部门编码');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.globalsetting where  type='合同用印申请单' and code='审计部经办人所属部门编码') = 0 
THEN
	insert into public.globalsetting(userid,type,code,value,message,updated_at,created_at,org_id)
	select 1,'合同用印申请单','审计部经办人所属部门编码','40-19','部门编码',now(),now(),id 
	From public.organizations where id not in(select org_id from public.globalsetting where  type='合同用印申请单' and code='审计部经办人所属部门编码');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.globalsetting where  type='设置用印提醒人' and code='合同审批时设置用印提醒人') = 0 
THEN
	insert into public.globalsetting(userid,type,code,value,message,updated_at,created_at,org_id)
	select 1,'设置用印提醒人','合同审批时设置用印提醒人','101009','合同审批人用户编码，有多个时用 , 号隔开',now(),now(),id 
	From public.organizations where id not in(select org_id from public.globalsetting where  type='设置用印提醒人' and code='合同审批时设置用印提醒人');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.groups where  name='super_admin') = 0 
THEN
	insert into public.groups(org_id,type,name,permissions,created_at)
	select 	id ,'builtin','super_admin',array['admin','super_admin'],now()
	From public.organizations where id not in(select org_id from public.groups where  name='super_admin');
END IF;
END
$do$;
DO
$do$
BEGIN
IF (select  count(*) cc from public.groups where  name='lawyer') = 0 
THEN
	insert into public.groups(org_id,type,name,permissions,created_at)
	select 	id ,'regular','lawyer',array['lawyer'],now()
	From public.organizations where id not in(select org_id from public.groups where  name='lawyer');
END IF;
END
$do$;

DO
$do$
BEGIN
IF (select  count(*) cc from public.templates where  id=0) = 0 
THEN
	insert into public.templates(id,userid ,code ,name ,type,template,updated_at,created_at,org_id,orgunit_id)
	select 0,1 ,'word' ,'WORD模板' ,array['wordmb'],
	'',
	now(),now(),id,0 
	From public.organizations where id not in(select org_id from public.templates where name='WORD模板');
END IF;
END
$do$;
	-----------查看是否已安装扩展
DO
$do$
BEGIN
IF (select  count(*) cc from pg_extension WHERE extname='uuid-ossp') = 0 
THEN
	create extension "uuid-ossp";
END IF;
END
$do$;
DO
$do$
BEGIN
IF (select  count(*) cc from public.enums where code='contracttype') = 0 
THEN
   insert into public.enums(code,name,items,is_builtin,org_id)
select   'contracttype','合同类型',array['{"key":"购置货物","value":"购置货物","label":"购置货物","title":"购置货物","children":[{"key":"固定资产","value":"固定资产","label":"固定资产","title":"固定资产"},
{"key":"卫材","value":"卫材","label":"卫材","title":"卫材"},{"key":"后勤物资","value":"后勤物资","label":"后勤物资","title":"后勤物资"},
{"key":"其他物资","value":"其他物资","label":"其他物资","title":"其他物资"}]}'::json,
'{"key":"租赁","value":"租赁","label":"租赁","title":"租赁","children":[{"key":"出租","value":"出租","label":"出租","title":"出租"},
{"key":"承租","value":"承租","label":"承租","title":"承租"}]}'::json,
'{"key":"购买服务","value":"购买服务","label":"购买服务","title":"购买服务","children":[{"key":"后勤服务","value":"后勤服务","label":"后勤服务","title":"后勤服务"},
{"key":"设备维修维保","value":"设备维修维保","label":"设备维修维保","title":"设备维修维保"},{"key":"宣传","value":"宣传","label":"宣传","title":"宣传"},
{"key":"其他服务","value":"其他服务","label":"其他服务","title":"其他服务"}]}'::json,
'{"key":"捐赠","value":"捐赠","label":"捐赠","title":"捐赠","children":[{"key":"接受捐赠","value":"接受捐赠","label":"接受捐赠","title":"接受捐赠"},
{"key":"对外捐赠","value":"对外捐赠","label":"对外捐赠","title":"对外捐赠"}]}'::json,
'{"key":"基建项目","value":"基建项目","label":"基建项目","title":"基建项目","children":[{"key":"基本建设","value":"基本建设","label":"基本建设","title":"基本建设"},
{"key":"维修改造","value":"维修改造","label":"维修改造","title":"维修改造"},{"key":"建设服务","value":"建设服务","label":"建设服务","title":"建设服务"}]}'::json,
'{"key":"科研教学","value":"科研教学","label":"科研教学","title":"科研教学","children":[{"key":"科研课题","value":"科研课题","label":"科研课题","title":"科研课题"},
{"key":"GCP","value":"GCP","label":"GCP","title":"GCP"}]}'::json,
'{"key":"信息化","value":"信息化","label":"信息化","title":"信息化","children":[{"key":"硬件","value":"硬件","label":"硬件","title":"硬件"},
{"key":"软件","value":"软件","label":"软件","title":"软件"},{"key":"信息服务","value":"信息服务","label":"信息服务","title":"信息服务"}]}'::json,
'{"key":"承 包","value":"承 包","label":"承 包","title":"承 包","children":[{"key":"承包","value":"承包","label":"承包","title":"承包"}]}'::json,
'{"key":"对外合作","value":"对外合作","label":"对外合作","title":"对外合作","children":[{"key":"无偿合作","value":"无偿合作","label":"无偿合作","title":"无偿合作"},
{"key":"有偿合作","value":"有偿合作","label":"有偿合作","title":"有偿合作"},{"key":"框架协议","value":"框架协议","label":"框架协议","title":"框架协议"}]}'::json,
'{"key":"其 他","value":"其 他","label":"其 他","title":"其 他","children":[{"key":"其他","value":"其他","label":"其他","title":"其他"}]}'::json],true,id 
From public.organizations where id not in(select org_id from public.enums where code='contracttype');
END IF;
END
$do$;	
ALTER TABLE public.workflow OWNER TO  redash;
ALTER TABLE public.workflow_action OWNER TO  redash;
ALTER TABLE public.workflow_bill_state OWNER TO  redash;
ALTER TABLE public.workflow_state OWNER TO  redash;
ALTER TABLE public.workflow_transition OWNER TO  redash;
ALTER TABLE public.tabletype OWNER TO  redash;
ALTER TABLE public.tablefield OWNER TO  redash;
ALTER TABLE public.employeetemp OWNER TO  redash;
ALTER TABLE public.customertemp OWNER TO  redash;
ALTER TABLE public.contractapply OWNER TO  redash;
ALTER TABLE public.contracts OWNER TO  redash;
ALTER TABLE public.contractseal OWNER TO  redash;
ALTER TABLE public.contractrecord OWNER TO  redash;
ALTER TABLE public.contractchange OWNER TO  redash;
ALTER TABLE public.contractprogress OWNER TO  redash;
ALTER TABLE public.contractsettle OWNER TO  redash;
ALTER TABLE public.contractstop OWNER TO  redash;
ALTER TABLE public.contractfinish OWNER TO  redash;
ALTER TABLE public.contractamount OWNER TO  redash;
ALTER TABLE public.attaches OWNER TO  redash;
ALTER TABLE public.templates OWNER TO  redash;
ALTER TABLE public.contractactivity OWNER TO redash;
ALTER TABLE public.globalsetting OWNER TO redash;
ALTER TABLE public.contractpayfor OWNER TO contractpayfor;

----------------------------------------------------------
commit;
